# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter

import pymysql

BATCH_VALUE = 1000  # 设置批量执行sql容器大小


# 使用管道将数据写入数据库
class DivisionPipeline:
    # 初始化
    def __init__(self):
        self.conn = pymysql.connect(host="192.168.2.3",
                                    port=3306,
                                    user="root",
                                    password="123456",
                                    db="division",
                                    charset="utf8mb4")
        self.cursor = self.conn.cursor()
        self.data = []  # 创建批处理sql容器
        self.city_data = []
        self.county_data = []
        self.town_data = []
        self.village_data = []

    # 拿到数据时触发
    def process_item(self, item, spider):
        if item["grade"] == 1:
            self.data.append((item["code"], item["parent_code"], item["name"], item["grade"]))  # 向sql容器追加数据
            # 将数据批量存入容器
            if len(self.data) == BATCH_VALUE:
                self._write_to_db()
                self.data.clear()  # 提交完成之后清空sql容器
        elif item["grade"] == 2:
            self.city_data.append((item["code"], item["parent_code"], item["name"], item["grade"]))
            if len(self.city_data) == BATCH_VALUE:
                self._write_to_db_city()
                self.city_data.clear()
        elif item["grade"] == 3:
            self.county_data.append((item["code"], item["parent_code"], item["name"], item["grade"]))
            if len(self.county_data) == BATCH_VALUE:
                self._write_to_db_county()
                self.county_data.clear()
        elif item["grade"] == 4:
            self.town_data.append((item["code"], item["parent_code"], item["name"], item["grade"]))
            if len(self.town_data) == BATCH_VALUE:
                self._write_to_db_town()
                self.town_data.clear()
        elif item["grade"] == 5:
            self.village_data.append((item["code"], item["parent_code"], item["name"], item["grade"]))
            if len(self.village_data) == BATCH_VALUE:
                self._write_to_db_village()
                self.village_data.clear()
        return item

    # 爬虫关闭时触发
    def close_spider(self, spider):
        # 程序关闭时检查sql容器中是否全部执行完成
        if len(self.data) > 0:
            self._write_to_db()
        if len(self.city_data) > 0:
            self._write_to_db_city()
        if len(self.county_data) > 0:
            self._write_to_db_county()
        if len(self.town_data) > 0:
            self._write_to_db_town()
        if len(self.village_data) > 0:
            self._write_to_db_village()
        # 提交sql语句
        self.conn.commit()
        self.conn.close()

    # 抽离写入数据库方法
    def _write_to_db(self):
        sql = "INSERT INTO province (code, parent_code, name, grade) values (%s, %s, %s, %s)"
        self.cursor.executemany(sql, self.data)  # 执行多条sql语句
        self.conn.commit()  # 提交

    def _write_to_db_city(self):
        sql = "INSERT INTO city (code, parent_code, name, grade) values (%s, %s, %s, %s)"
        self.cursor.executemany(sql, self.city_data)
        self.conn.commit()

    def _write_to_db_county(self):
        sql = "INSERT INTO county (code, parent_code, name, grade) values (%s, %s, %s, %s)"
        self.cursor.executemany(sql, self.county_data)
        self.conn.commit()

    def _write_to_db_town(self):
        sql = "INSERT INTO town (code, parent_code, name, grade) values (%s, %s, %s, %s)"
        self.cursor.executemany(sql, self.town_data)
        self.conn.commit()

    def _write_to_db_village(self):
        sql = "INSERT INTO village (code, parent_code, name, grade) values (%s, %s, %s, %s)"
        self.cursor.executemany(sql, self.village_data)
        self.conn.commit()






